/**
 * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved.
 */
package com.jeeplus.modules.ap.web;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolationException;

import com.google.gson.Gson;
import com.jeeplus.modules.ap.entity.Yue;
import com.jeeplus.modules.ap.service.YueService;
import com.jeeplus.modules.infor.entity.Department;
import com.jeeplus.modules.infor.entity.Project;
import com.jeeplus.modules.infor.service.DepartmentService;
import com.jeeplus.modules.infor.service.ProjectService;
import com.jeeplus.modules.sys.utils.UserUtils;
import org.apache.shiro.authz.annotation.Logical;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.google.common.collect.Lists;
import com.jeeplus.common.utils.DateUtils;
import com.jeeplus.common.config.Global;
import com.jeeplus.common.json.AjaxJson;
import com.jeeplus.core.persistence.Page;
import com.jeeplus.core.web.BaseController;
import com.jeeplus.common.utils.StringUtils;
import com.jeeplus.common.utils.excel.ExportExcel;
import com.jeeplus.common.utils.excel.ImportExcel;
import com.jeeplus.modules.ap.entity.Detil;
import com.jeeplus.modules.ap.service.DetilService;

/**
 * 财务收支明细Controller
 * @author 李超辉
 * @version 2018-05-30
 */
@Controller
@RequestMapping(value = "${adminPath}/ap/detil")
public class DetilController extends BaseController {

	@Autowired
	private DetilService detilService;
	@Autowired
	private YueService yueService;
	@Autowired
	private JdbcTemplate jdbc;
	@Autowired
	private ProjectService projectService;
	@Autowired
	private DepartmentService departmentService;
	
	@ModelAttribute
	public Detil get(@RequestParam(required=false) String id) {
		Detil entity = null;
		if (StringUtils.isNotBlank(id)){
			entity = detilService.get(id);
		}
		if (entity == null){
			entity = new Detil();
		}
		return entity;
	}
	
	/**
	 * 财务收支明细列表页面
	 */
	@RequiresPermissions("ap:detil:list")
	@RequestMapping(value = {"list", ""})
	public String list() {
		return "modules/ap/detilList";
	}

	/**
	 * 财务收支明细列表页面
	 */
	@RequiresPermissions("ap:detil:list")
	@RequestMapping(value = {"list1"})
	public String list1() {
		return "modules/ap/1/detilList";
	}
	
		/**
	 * 财务收支明细列表数据
	 */
	@ResponseBody
	@RequiresPermissions("ap:detil:list")
	@RequestMapping(value = "data")
	public Map<String, Object> data(Detil detil, HttpServletRequest request, HttpServletResponse response, Model model) {
	String pro= UserUtils.getUseroffice();
	if(!pro.equals("总部")){
		detil.setPro(pro);
	}
			Page<Detil> page = detilService.findPage(new Page<Detil>(request, response), detil);
		return getBootstrapData(page);
	}

	/**
	 * 查看，增加，编辑财务收支明细表单页面
	 */
	@RequiresPermissions(value={"ap:detil:view","ap:detil:add","ap:detil:edit"},logical=Logical.OR)
	@RequestMapping(value = "form")
	public String form(Detil detil, Model model) {
		model.addAttribute("detil", detil);
		return "modules/ap/detilForm";
	}

	@ResponseBody
	@RequestMapping(value="cx")
	public String cx(String min,String max) throws ParseException {
		String pro=UserUtils.getUseroffice();
		if(!pro.equals("总部")){
			cha(min,max,pro);
       }else{
			List<Project> list=projectService.findList(new Project());
			for(Project p:list){
				cha(min,max,p.getName());
			}
		}
	return "";
	}

	public  void  cha(String min,String max,String pro) throws ParseException {
		Detil detil=new Detil();
		detil.setPro(pro);
		Department ddd=new Department();
		ddd.setProject(projectService.findUniqueByProperty("name",pro));
		List<Department> l=departmentService.findList(ddd);
		if(l!=null&&l.size()>0){
			detil.setRemarks(l.get(0).getName());
		}
		List<Map<String ,Object>> map=jdbc.queryForList("select * from ap_day_detil where pro='"+pro+"' and  to_days(date) = to_days('"+min+"')-1");
		List<Map<String ,Object>> map1=jdbc.queryForList("select * from ap_day_detil where pro='"+pro+"' and  to_days(date) = to_days('"+max+"')-1");
        if(map==null||map.size()<1){
        	detil.setZqxj("0");
        	detil.setZqyk("0");
        	detil.setZqjy("0");
		}else{
			detil.setZqxj(map.get(0).get("jyxj")+"");
			detil.setZqyk(map.get(0).get("jyyk")+"");
			detil.setZqjy(map.get(0).get("zjy")+"");
		}
		if(map1==null||map1.size()<1){
			SimpleDateFormat s= new SimpleDateFormat("yyyy-MM-dd");
			Date d=s.parse(max);
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(new Date());
			calendar.add(Calendar.DAY_OF_MONTH, 1);
			Date zero = calendar.getTime();
			if(!zero.before(d)){
				detil.setJyxj("0");
				detil.setJyyk("0");
				detil.setJy("0");
			}else {
			Yue y=yueService.findUniqueByProperty("pro",pro);
			detil.setJyxj(y.getXj());
			detil.setJyyk(y.getYk());
			detil.setJy(y.getZje());}
		}else{
			detil.setJyxj(map1.get(0).get("jyxj")+"");
			detil.setJyyk(map1.get(0).get("jyyk")+"");
			detil.setJy(map1.get(0).get("zjy")+"");
		}

		jdbc.execute("delete from ap_detil where pro='"+pro+"'");
       Project p=projectService.findUniqueByProperty("name",pro);

		//备用金收入
		String sql="select * from ap_imprest where project_name='"+pro+"' and to_days(dzrq) >= to_days('"+min+"') and to_days(dzrq) < to_days('"+max+"') and document_status='已到账确认'";
		List<Map<String ,Object>> maps=jdbc.queryForList(sql);
		Double byj=0.0;
		Double byjxj=0.0;
		Double byjyk=0.0;
		for(Map<String,Object> m:maps){
			if((m.get("money")+"").equals("现金")){
				byjxj+=Double.parseDouble(m.get("payment_money")+"");
			}else{
				byjyk+=Double.parseDouble(m.get("payment_money")+"");
			}
			byj+=Double.parseDouble(m.get("payment_money")+"");
		}
		detil.setByjsr(byj+"");
		detil.setByjxj(byjxj+"");
		detil.setByjyk(byjyk+"");
		//预付款支出
		sql="select * from ap_advance where pro='"+pro+"' and to_days(rz) >= to_days('"+min+"') and to_days(rz) < to_days('"+max+"') and del_flag='0'";
		maps=jdbc.queryForList(sql);
		Double yfk=0.0;
		Double yfkxj=0.0;
		Double yfkyk=0.0;
		for(Map<String,Object> m:maps){
			yfk+=Double.parseDouble(m.get("sfzje")+"");
			yfkxj+=Double.parseDouble(m.get("sfxj")+"");
			yfkyk+=Double.parseDouble(m.get("sfyk")+"");
		}
		detil.setYfkzc(yfk+"");
		detil.setYfkxj(yfkxj+"");
		detil.setYfkyk(yfkyk+"");
		//回执结算支出（含货到）
		sql="select * from ap_recel where pro='"+pro+"' and to_days(rz) >= to_days('"+min+"') and to_days(rz) < to_days('"+max+"') and del_flag='0'";
		maps=jdbc.queryForList(sql);
		Double hzjs=0.0;
		Double hzxj=0.0;
		Double hzyk=0.0;
		for(Map<String,Object> m:maps){
			hzxj+=Double.parseDouble(m.get("money")+"")+Double.parseDouble(m.get("sfhd")+"");
			hzyk+=Double.parseDouble(m.get("oil")+"");
			hzjs+=Double.parseDouble(m.get("oil")+"")+Double.parseDouble(m.get("money")+"")+Double.parseDouble(m.get("sfhd")+"");
		}
		detil.setHzfkzc(hzjs+"");
		detil.setHzxj(hzxj+"");
		detil.setHzyk(hzyk+"");
		//其它收入
		sql="select * from ap_income where project_name='"+p.getId()+"' and to_days(update_date) >= to_days('"+min+"') and to_days(update_date) < to_days('"+max+"') and document_status='财务已审批'";
		maps=jdbc.queryForList(sql);
		Double rcsr=0.0;
		for(Map<String,Object> m:maps){
			rcsr+=Double.parseDouble(m.get("money")+"");
		}
		detil.setRcsr(rcsr+"");
		//其它支出
		sql="select * from ap_expenditure where project_name='"+p.getId()+"' and to_days(update_date) >= to_days('"+min+"') and to_days(update_date) < to_days('"+max+"') and document_status='财务已审批'";
		maps=jdbc.queryForList(sql);
		Double rczc=0.0;
		for(Map<String,Object> m:maps){
			rczc+=Double.parseDouble(m.get("money")+"");
		}
		detil.setFyzc(rczc+"");
		//运单其它收入支出
		sql="select * from ap_bill_add where pro='"+pro+"' and to_days(rz) >= to_days('"+min+"') and to_days(rz) < to_days('"+max+"')";
		maps=jdbc.queryForList(sql);
		Double xxf=0.0;
		Double bxf=0.0;
		Double qtsr=0.0;
		Double fhp=0.0;
		Double zxf=0.0;
		Double qtzc=0.0;
		Double fhpsr=0.0;
		for(Map<String,Object> m:maps){
			xxf+=Double.parseDouble(m.get("xxfsr")+"");
			bxf+=Double.parseDouble(m.get("bxfsr")+"");
			qtsr+=Double.parseDouble(m.get("qtsr")+"");
			fhp+=Double.parseDouble(m.get("fhpzc")+"");
			zxf+=Double.parseDouble(m.get("zxfzc")+"");
			qtzc+=Double.parseDouble(m.get("qtzc")+"");
			fhpsr+=Double.parseDouble(m.get("fhpsr")+"");
		}
		detil.setXxfsr(xxf+"");
		detil.setBxfsr(bxf+"");
		detil.setQtsr(qtsr+"");
		detil.setFhpzc(fhp+"");
		detil.setZxfzc(zxf+"");
		detil.setQtzc(qtzc+"");
		detil.setFhpsr(fhpsr+"");
		detil.setZsr((byj+xxf+bxf+rcsr+fhpsr+qtsr)+"");
		detil.setZzc((yfk+hzjs+fhp+zxf+rczc+qtzc)+"");
		detilService.save(detil);
	}

	@ResponseBody
	@RequestMapping(value="getYe")
	public Yue getYe(){
		String pro= UserUtils.getUseroffice();
		Yue yue=yueService.findUniqueByProperty("pro",pro);

		return yue;
	}
	@ResponseBody
	@RequestMapping(value="getYe0")
	public String  getYe0(){
		List<Yue> list=yueService.findList(new Yue());
		Gson g=new Gson();
		return g.toJson(list).toString();
	}
	/**
	 * 保存财务收支明细
	 */
	@ResponseBody
	@RequiresPermissions(value={"ap:detil:add","ap:detil:edit"},logical=Logical.OR)
	@RequestMapping(value = "save")
	public AjaxJson save(Detil detil, Model model, RedirectAttributes redirectAttributes) throws Exception{
		AjaxJson j = new AjaxJson();
		if (!beanValidator(model, detil)){
			j.setSuccess(false);
			j.setMsg("非法参数！");
			return j;
		}
		detilService.save(detil);//新建或者编辑保存
		j.setSuccess(true);
		j.setMsg("保存财务收支明细成功");
		return j;
	}
	
	/**
	 * 删除财务收支明细
	 */
	@ResponseBody
	@RequiresPermissions("ap:detil:del")
	@RequestMapping(value = "delete")
	public AjaxJson delete(Detil detil, RedirectAttributes redirectAttributes) {
		AjaxJson j = new AjaxJson();
		detilService.delete(detil);
		j.setMsg("删除财务收支明细成功");
		return j;
	}
	
	/**
	 * 批量删除财务收支明细
	 */
	@ResponseBody
	@RequiresPermissions("ap:detil:del")
	@RequestMapping(value = "deleteAll")
	public AjaxJson deleteAll(String ids, RedirectAttributes redirectAttributes) {
		AjaxJson j = new AjaxJson();
		String idArray[] =ids.split(",");
		for(String id : idArray){
			detilService.delete(detilService.get(id));
		}
		j.setMsg("删除财务收支明细成功");
		return j;
	}
	
	/**
	 * 导出excel文件
	 */
	@ResponseBody
	@RequiresPermissions("ap:detil:export")
    @RequestMapping(value = "export", method=RequestMethod.POST)
    public AjaxJson exportFile(Detil detil, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
		AjaxJson j = new AjaxJson();
		try {
            String fileName = "财务收支明细"+DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
            Page<Detil> page = detilService.findPage(new Page<Detil>(request, response, -1), detil);
    		new ExportExcel("财务收支明细", Detil.class).setDataList(page.getList()).write(response, fileName).dispose();
    		j.setSuccess(true);
    		j.setMsg("导出成功！");
    		return j;
		} catch (Exception e) {
			j.setSuccess(false);
			j.setMsg("导出财务收支明细记录失败！失败信息："+e.getMessage());
		}
			return j;
    }

	/**
	 * 导入Excel数据

	 */
	@RequiresPermissions("ap:detil:import")
    @RequestMapping(value = "import", method=RequestMethod.POST)
    public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
		try {
			int successNum = 0;
			int failureNum = 0;
			StringBuilder failureMsg = new StringBuilder();
			ImportExcel ei = new ImportExcel(file, 1, 0);
			List<Detil> list = ei.getDataList(Detil.class);
			for (Detil detil : list){
				try{
					detilService.save(detil);
					successNum++;
				}catch(ConstraintViolationException ex){
					failureNum++;
				}catch (Exception ex) {
					failureNum++;
				}
			}
			if (failureNum>0){
				failureMsg.insert(0, "，失败 "+failureNum+" 条财务收支明细记录。");
			}
			addMessage(redirectAttributes, "已成功导入 "+successNum+" 条财务收支明细记录"+failureMsg);
		} catch (Exception e) {
			addMessage(redirectAttributes, "导入财务收支明细失败！失败信息："+e.getMessage());
		}
		return "redirect:"+Global.getAdminPath()+"/ap/detil/?repage";
    }
	
	/**
	 * 下载导入财务收支明细数据模板
	 */
	@RequiresPermissions("ap:detil:import")
    @RequestMapping(value = "import/template")
    public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) {
		try {
            String fileName = "财务收支明细数据导入模板.xlsx";
    		List<Detil> list = Lists.newArrayList(); 
    		new ExportExcel("财务收支明细数据", Detil.class, 1).setDataList(list).write(response, fileName).dispose();
    		return null;
		} catch (Exception e) {
			addMessage(redirectAttributes, "导入模板下载失败！失败信息："+e.getMessage());
		}
		return "redirect:"+Global.getAdminPath()+"/ap/detil/?repage";
    }

}